﻿using System;
using Finisar.SQLite;

namespace MyMovies
{
    internal class TableGenre
    {
        private static string create = @"CREATE TABLE [Genre] (
                                    [ID] INTEGER  NOT NULL PRIMARY KEY,
                                    [Description] VARCHAR(64)  NULL,
                                    [TimeStamp] DATE NULL
                                    );";

        public static bool Create()
        {
            return SqlLiteManagement.ExecuteNonQuery(create) >= 0;
        }

        public static int SearchGenre(string genre, DateTime scanDate)
        {
            int ID = -1;
            DateTime date = DateTime.Today;
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.String);
            p1.Value = genre;

            string query = "SELECT [ID], [TIMESTAMP] FROM [Genre] WHERE [Description] = @p1";

            if (SqlLiteManagement.ExecuteReader(query, new SQLiteParameter[] { p1 }) != null)
            {
                while (SqlLiteManagement.Read())
                {
                    ID = SqlLiteManagement.GetValue<int>("[ID]");
                    date = SqlLiteManagement.GetValue<DateTime>("[TIMESTAMP]");
                }
            }
            SqlLiteManagement.ReaderClose();

            if (date.ToString() != scanDate.ToString() && ID != -1)
                UpdateTimeStamp(ID, scanDate);

            return ID;
        }

        public static string FindGenreByID(int id)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;

            string query = "SELECT [Description] FROM [Genre] WHERE [ID] = @p1";

            object result = SqlLiteManagement.ExecuteScalar(query, new SQLiteParameter[] { p1 });

            return result == null
                ? string.Empty
                : (string)Convert.ChangeType(result, typeof(string));
        }

        public static int SearchInsert(string genre, DateTime scanDate)
        {
            int id = SearchGenre(genre, scanDate);
            if (id >= 0)
                return id;

            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.DateTime);
            p1.Value = scanDate;
            string query = string.Format("INSERT INTO [Genre] ([Description], [TIMESTAMP]) VALUES ('{0}', @p1)", genre, scanDate);
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });

            return SearchGenre(genre, scanDate);
        }

        public static void FillGroup(System.Windows.Forms.ListViewGroupCollection group)
        {
            group.Clear();

            string query = "SELECT [Description] FROM [Genre]";

            if (SqlLiteManagement.ExecuteReader(query) != null)
            {
                while (SqlLiteManagement.Read())
                {
                    string description = SqlLiteManagement.GetValue<string>("[Description]");

                    group.Add(description, description);
                }
            }
            SqlLiteManagement.ReaderClose();
        }

        public static void RemoveALL()
        {
            string query = "DELETE FROM [Genre]";
            SqlLiteManagement.ExecuteNonQuery(query);
        }

        public static void RemoveOld(DateTime scanDate)
        {
            string query = "DELETE FROM [Genre] WHERE [TIMESTAMP] < @p1";
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.DateTime);
            p1.Value = scanDate;

            SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }

        public static void UpdateTimeStamp(int ID, DateTime scanDate)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.DateTime);
            p1.Value = scanDate;

            string query = string.Format("UPDATE [Genre] SET [TIMESTAMP] = @p1 WHERE [ID] = '{0}'", ID.ToString());
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { p1 });
        }
    }
}